package com.yzyz.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.yzyz.bean.hotel.HotelBean;
import com.yzyz.bean.scenic.PictureBean;
import com.yzyz.dao.IHotelDao;
import com.yzyz.utils.Star;
import com.yzyz.utils.ValidateUtils;

/**
 * 酒店数据库操作对象
 * @author david.sun
 * @date 2010-6-4
 */
public class HotelDaoImpl implements IHotelDao {

	private JdbcTemplate jdbcTemplate;
	@SuppressWarnings("unchecked")
	@Override
	public List<HotelBean> paginate(int start, int limit) {
		String sql = "select * from hotel limit " + start + ", " + limit;
		return this.jdbcTemplate.query(sql, new HotelRowMapper());
	}

	@Override
	public int delete(int id) {
		String sql = "delete from hotel where id=" + id;
		return this.jdbcTemplate.update(sql);
	}

	@SuppressWarnings("unchecked")
	@Override
	public Object get(int id) {
		String sql = "select * from hotel where id=" + id;
		List<HotelBean> hotels =  this.jdbcTemplate.query(sql, new HotelRowMapper());
		if(hotels == null || hotels.size() != 1) {
			return null;
		}
		return hotels.get(0);
	}

	@Override
	public int getCount(int ownerId) {
		String sql = "select count(*) from hotel";
		return this.jdbcTemplate.queryForInt(sql);
	}

	@Override
	public int save(Object object) {
		final HotelBean hotel = (HotelBean)object;
		final String sql = "insert into hotel (name, address, available, description, status, level, price) values (?,?,?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		this.jdbcTemplate.update(new PreparedStatementCreator() {
			public java.sql.PreparedStatement createPreparedStatement(
					Connection conn) throws SQLException {
				java.sql.PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, hotel.getName());
				ps.setString(2, hotel.getAddress());
				ps.setBoolean(3, hotel.isAvailable());
				ps.setString(4, hotel.getDescription());
				ps.setInt(5, hotel.getStatus().getType());
				ps.setInt(6, hotel.getLevel());
				ps.setString(7, hotel.getPrice());
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();
	}

	@Override
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;

	}

	@Override
	public void update(Object object) {
		HotelBean hotel = (HotelBean)object;
		String sql = "update hotel set name=?, address=?, description=?, status=?, level=?,price=? where id=?";
		this.jdbcTemplate.update(sql, new Object[]{hotel.getName(), hotel.getAddress(), hotel.getDescription(),hotel.getStatus().getType(), hotel.getLevel(),hotel.getPrice(), hotel.getId()});

	}

	@Override
	public int updateAvailable(int id, boolean available) {
		String sql = "update hotel set available=? where id=?";
		return this.jdbcTemplate.update(sql, new Object[]{available, id});
	}
	
	private class HotelRowMapper implements RowMapper{

		@Override
		public Object mapRow(ResultSet rs, int index) throws SQLException {
			HotelBean hotel = new HotelBean();
			hotel.setAddress(rs.getString("address"));
			hotel.setDescription(rs.getString("description"));
			hotel.setId(rs.getInt("id"));
			hotel.setName(rs.getString("name"));
			hotel.setStatus(Star.getInstance(rs.getInt("status")));
			hotel.setLevel(rs.getInt("level"));
			hotel.setAvailable(rs.getBoolean("available"));
			hotel.setPrice(rs.getString("price"));
			return hotel;
		}
		
	}

	@Override
	public int savePicture(final PictureBean picture) {
		final String sql = "insert into hotel_picture (ownerId, contents, name, description) values(?,?,?,?)";
		KeyHolder key = new GeneratedKeyHolder();
		this.jdbcTemplate.update(new PreparedStatementCreator(){

			@Override
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement st = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				st.setInt(1, picture.getOwnerId());
				st.setBytes(2, picture.getContents());
				st.setString(3, picture.getName());
				st.setString(4, picture.getDescription());
				return st;
			}}, key);
		return key.getKey().intValue();
	}

	@Override
	public int saveScenic(final int hotelId, final int scenicId) {
		final String sql = "insert into hotel_scenic (hotelId, scenicId) values(?,?)";
		KeyHolder key = new GeneratedKeyHolder();
		this.jdbcTemplate.update(new PreparedStatementCreator(){

			@Override
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement st = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				st.setInt(1, hotelId);
				st.setInt(2, scenicId);
				return st;
			}}, key);
		return key.getKey().intValue();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<HotelBean> paginate(int start, int limit, String hotelName,
			String scenicName) {
		if(ValidateUtils.isNull(hotelName)) {
			
			String sql = "select * from hotel, hotel_scenic, scenic " +
					"where name like '%?%' and hotel_scenic.scenicId=scenic.id " +
					"and hotel_scenic.hotelId=hotel.name order by hotel.level desc limit " + start + ", " + limit;
			List<HotelBean> list = this.jdbcTemplate.query(sql, new Object[]{hotelName}, new RowMapper(){

				@Override
				public Object mapRow(ResultSet rs, int arg1)
						throws SQLException {
					HotelBean hotel = new HotelBean();
					hotel.setAddress(rs.getString("hotel.address"));
					hotel.setDescription(rs.getString("hotel.description"));
					hotel.setId(rs.getInt("hotel.id"));
					hotel.setName(rs.getString("hotel.name"));
					hotel.setStatus(Star.getInstance(rs.getInt("hotel.status")));
					hotel.setLevel(rs.getInt("hotel.level"));
					hotel.setAvailable(rs.getBoolean("hotel.available"));
					hotel.setPrice(rs.getString("hotel.price"));
					return hotel;
				}});
			return list;
			
		} else {
			String sql = "select * from hotel, hotel_scenic, scenic " +
			"where hotel.name like '%?%' and scenic.name like'%?%' and hotel_scenic.scenicId=scenic.id " +
			"and hotel_scenic.hotelId=hotel.name order by hotel.level desc limit " + start + ", " + limit;
			List<HotelBean> list = this.jdbcTemplate.query(sql, new Object[]{hotelName, scenicName}, new RowMapper(){
	
			@Override
			public Object mapRow(ResultSet rs, int arg1)
					throws SQLException {
				HotelBean hotel = new HotelBean();
				hotel.setAddress(rs.getString("hotel.address"));
				hotel.setDescription(rs.getString("hotel.description"));
				hotel.setId(rs.getInt("hotel.id"));
				hotel.setName(rs.getString("hotel.name"));
				hotel.setStatus(Star.getInstance(rs.getInt("hotel.status")));
				hotel.setLevel(rs.getInt("hotel.level"));
				hotel.setAvailable(rs.getBoolean("hotel.available"));
				hotel.setPrice(rs.getString("hotel.price"));
				return hotel;
			}});
			return list;
		}
	}

}

